package evaluacredito.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javax.sql.DataSource;

import evaluacredito.excepcion.DAOExcepcion;
import evaluacredito.modelo.Persona;
import evaluacredito.util.ConexionBD;

public class PersonaDAOImpl extends BaseDAO implements PersonaDAO {
	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public Collection<Persona> buscarPorNombre(String nombres)
			throws DAOExcepcion {
		String query = "select idPersona, nroDocumento,apPaterno,apMaterno,nombres from persona where nombres like ?";
		Collection<Persona> lista = new ArrayList<Persona>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//con = dataSource.getConnection();
			con = ConexionBD.obtenerConexion();

			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + nombres + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Persona pe = new Persona();
				pe.setIdPersona(rs.getInt("idPersona"));
				pe.setNroDocumento(rs.getString("nroDocumento"));
				pe.setApPaterno(rs.getString("apPaterno"));
				pe.setApMaterno(rs.getString("apMaterno"));
				pe.setNombres(rs.getString("nombres"));

				lista.add(pe);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}

	public Persona insertar(Persona pe) throws DAOExcepcion {
		String query = "INSERT INTO persona(nroDocumento,apPaterno,apMaterno,nombres) VALUES (?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
//			con = dataSource.getConnection();
			con = ConexionBD.obtenerConexion();

			stmt = con.prepareStatement(query);
			stmt.setString(1, pe.getNroDocumento().toUpperCase());
			stmt.setString(2, pe.getApPaterno().toUpperCase());
			stmt.setString(3, pe.getApMaterno().toUpperCase());
			stmt.setString(4, pe.getNombres().toUpperCase());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			} // Obtener el ultimo id
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				pe.setIdPersona(rs.getInt(1));
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return pe;
	}

	public Persona obtener(String nroDocumento) throws DAOExcepcion {
		Persona pe = new Persona();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select idPersona, nroDocumento,apPaterno,apMaterno,nombres from persona where nroDocumento=?";
//			con = dataSource.getConnection();
			con = ConexionBD.obtenerConexion();

			stmt = con.prepareStatement(query);
			stmt.setString(1, nroDocumento);
			rs = stmt.executeQuery();
			if (rs.next()) {
				pe.setIdPersona(rs.getInt("idPersona"));
				pe.setNroDocumento(rs.getString("nroDocumento"));
				pe.setApPaterno(rs.getString("apPaterno"));
				pe.setApMaterno(rs.getString("apMaterno"));
				pe.setNombres(rs.getString("nombres"));

			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return pe;
	}

	public void eliminar(String nroDocumento) throws DAOExcepcion {
		String query = "DELETE FROM persona WHERE idPersona=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
//			con = dataSource.getConnection();
			con = ConexionBD.obtenerConexion();

			stmt = con.prepareStatement(query);
			stmt.setString(1, nroDocumento);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public Persona actualizar(Persona pe) throws DAOExcepcion {
		// Actualizar los campos para la actualizacion
		String query = "update persona set nroDocumento=?,apPaterno=?,apMaterno=?,nombres=? where idPersona=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
//			con = dataSource.getConnection();
			con = ConexionBD.obtenerConexion();

			stmt = con.prepareStatement(query);
			stmt.setString(1, pe.getNroDocumento().toUpperCase());
			stmt.setString(2, pe.getApPaterno().toUpperCase());
			stmt.setString(3, pe.getApMaterno().toUpperCase());
			stmt.setString(4, pe.getNombres().toUpperCase());
			stmt.setInt(5, pe.getIdPersona());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return pe;
	}

	public Collection<Persona> listar() throws DAOExcepcion {
		Collection<Persona> c = new ArrayList<Persona>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//con = dataSource.getConnection();
			con = ConexionBD.obtenerConexion();

			// Actualizar el query para la DB
			String query = "select idPersona, nroDocumento,apPaterno,apMaterno,nombres from persona";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Persona pe = new Persona();
				pe.setIdPersona(rs.getInt("idPersona"));
				pe.setNroDocumento(rs.getString("nroDocumento"));
				pe.setApPaterno(rs.getString("apPaterno"));
				pe.setApMaterno(rs.getString("apMaterno"));
				pe.setNombres(rs.getString("nombres"));
				c.add(pe);
			}
			//System.out.println(c);
	
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

}
